<?php
echo '<pre>';

include('../config.php');
include_once('../api/class.mysqli_database.php');
include('../api/xml2json.php');

function replace_unicode_escape_sequence($match) {
	return mb_convert_encoding(pack('H*', $match[1]), 'UTF-8', 'UCS-2BE');
}

$conString = sprintf("server=%s; database=%s; username=%s; password=%s; charset=UTF8;", $mysql_host, $mysql_db, $mysql_user, $mysql_pass);
$db = new MysqliDatabase($conString);
$db->set_charset("utf8");
#$charset = $db->character_set_name();
#printf ("Current character set is %s\n", $charset);

$src = 'http://gbrds.gbif.org/registry/extensions.json';

$srcJson = file_get_contents($src);
$extensions = json_decode($srcJson,true);

$rcdQuery = "SELECT max(recordID) rd FROM `xref`";
$ret = $db->query_one($rcdQuery);
$recordId = is_null($ret) ? 0: $ret->rd;
$tmpId = $recordId;

$lookUpTerms = array();
$lookUpTermsQuery = ' SELECT `recordID`,`term` FROM `terms` ';
$lookUps = $db->query_all($lookUpTermsQuery);
if(is_array($lookUps) && count($lookUps)) {
	foreach($lookUps as $lookUp) {
		$lookUpTerms[$lookUp->term] = $lookUp->recordID;
	}
}

if(is_array($extensions['extensions']) && count($extensions['extensions'])) {
	foreach($extensions['extensions'] as $extension) {

#	 if($i++ > 1) exit();

		$xmlStringContents = @file_get_contents($extension['url']);
		$xmlStringContents = str_replace('dc:','',$xmlStringContents);
		$contents = str_replace('@','', @xml2json::transformXmlStringToJson(utf8_decode($xmlStringContents)));
		$contents = preg_replace_callback('/\\\\u([0-9a-f]{4})/i', 'replace_unicode_escape_sequence', $contents);
		$contents = json_decode($contents, true);

		$attributes = $contents['extension']['attributes'];
		if(is_array($attributes) && count($attributes)) {
			$code = $attributes['title'];
			$codeQuery = sprintf(" INSERT IGNORE INTO `code` (`code`,`codeAbbreviation`,`namespace`,`rowType`,`description`,`link`,`type`) VALUES  ('%s','%s','%s','%s','%s','%s','darwincore') "
				, mysql_escape_string($attributes['title'])
				, mysql_escape_string($attributes['name'])
				, mysql_escape_string($attributes['namespace'])
				, mysql_escape_string($attributes['rowType'])
				, mysql_escape_string($attributes['description'])
				, mysql_escape_string($extension['url'])
			);
			$db->query($codeQuery);
		}

		$properties = $contents['extension']['property'];
		if(is_array($properties) && count($properties)) {
			foreach($properties as $property) {

				if(!isset($lookUpTerms[$property['attributes']['name']])) {

					$db->query(" INSERT INTO `xref` SET `type` = 'see' ");
					$tmpId = $db->insert_id;

					$recordId = $tmpId;
					$lookUpTerms[$property['attributes']['name']] = $recordId;
					if($property['attributes']['name'] != '') {
						$query = sprintf(" INSERT IGNORE INTO `terms` (`recordID`,`term`,`termStatus`, `termType`,`modified_date`,`created_date`) VALUES (%d, '%s', 'Primary', '%s', NOW(), NOW()) ", $recordId, mysql_escape_string($property['attributes']['name']),mysql_escape_string($property['attributes']['group']));
	
						if($db->query($query)) {
							$termCount++;
						}
						
					}
				} else {
					$recordId = $lookUpTerms[$property['attributes']['name']];
				}

				if($property['attributes']['group']){
					$db->query(sprintf(" INSERT IGNORE INTO `termType` SET `termType` = '%s' ", mysql_escape_string($property['attributes']['group'])));
				}

				# $description = ($property['attributes']['description'] == '') ? 'No definition given.' : $property['attributes']['description'];

				$description = $property['attributes']['description'];
				$required = ($property['attributes']['required'] == 'true') ? 1 : 0;

				$query = sprintf(" INSERT INTO `definitions` (`recordID`,`definition`,`code`, `namespace`,`qualName`,`termType`,`dataType`,`examples`,`required`,`modified_date`,`created_date`) VALUES (%d,'%s','%s', '%s','%s','%s', '%s','%s', '%s', NOW(), NOW()) "
					, $recordId
					, mysql_escape_string($description)
					, mysql_escape_string($code)
					, mysql_escape_string($property['attributes']['namespace'])
					, mysql_escape_string($property['attributes']['qualName'])
					, mysql_escape_string($property['attributes']['group'])
					, mysql_escape_string($property['attributes']['type'])
					, mysql_escape_string($property['attributes']['examples'])
					, $required
				);
		
				if($db->query($query)) {
					$defenitionCount++;
				}
			}
		}

	} # extension foreach
} # extensions

print '<br> Total Terms Inserted : ' . $termCount;
print '<br> Total Defenitions Inserted : ' . $defenitionCount;

?>